<?php
/*
 * Camelot PHP Tools
 * This example illustrates how to download a List Item Attachement
 *
 * @version 2.0
 * @author Bendsoft
 * @package PHP Tools
 * @subpackage Camelot
 * @license FreeBSD License (www.bendsoft.com/licensing/)
 *
 * Note! 
 * Set up settings.php before using this file
 */
error_reporting(E_COMPILE_ERROR);
ini_set('display_errors', '1');


// Load all required files for Camelot PHP Tools
require_once '../loader.php';
?>

<?php include 'base/head.php'; ?>

<h2>Example SELECT Commands</h2>
<p class="comment">For the full select documentation please visit <a href="http://www.bendsoft.com/documentation/camelot-net-connector/latest/sql-statement-syntax/select/">http://www.bendsoft.com/documentation/camelot-net-connector/latest/sql-statement-syntax/select/</a></p>
<form method="post">
    <div class="query">
        <label>Simple SELECT</label> 
        <input type="text" name="select1" value="<?php echo ((!isset($_POST['select1'])) || !$_POST ) ? 'SELECT * FROM [Tasks]' : $_POST['select1'] ?>" style="width: 400px;" />
        <input type="submit" name="select1_submit" value="Submit" />
    </div>

    <div class="query">
        <label>SELECT with view name</label> 
        <input type="text" name="select2" value="<?php echo ((!isset($_POST['select2'])) || !$_POST ) ? 'SELECT * FROM [Tasks].[My Tasks]' : $_POST['select2'] ?>" style="width: 400px;" />
        <input type="submit" name="select2_submit" value="Submit" />
        <p class="comment">You can add a [view name] or [all] after the library or list name, I.e. [Tasks].[My Tasks] or [Tasks].[All]. The [All will disable the * select and retrieve all columns]</p>
    </div>

    <div class="query">
        <label>Explain SELECT</label> 
        <input type="text" name="select3" value="<?php echo ((!isset($_POST['select3'])) || !$_POST ) ? 'EXPLAIN SELECT * FROM [Tasks] WHERE ID > 1 GROUP BY Title' : $_POST['select3'] ?>" style="width: 400px;" />
        <input type="submit" name="select3_submit" value="Submit" />
    </div>

    <div class="query">
        <label>SELECT with Join</label> 
        <textarea name="selectJoin" style="width: 400px; height: 80px;">
<?php echo ((!isset($_POST['selectJoin'])) || !$_POST ) ? "SELECT Customers.Title AS Name, Log.Title AS Event, Log.Created \nFROM Customers \nINNER JOIN Log ON Log.Customer = Customers.ID \nWHERE Customers.ID = 6 ORDER BY Log.Created ASC" : $_POST['selectJoin'] ?>
        </textarea>
        <input type="submit" name="selectJoin_submit" value="Submit" />
    </div>

    <div>
        <label>Render as table
            <input type="checkbox" name="renderAsTable" <?php echo ((isset($_POST['renderAsTable'])) || !$_POST ) ? 'checked="checked"' : '' ?>  />
        </label>
        <p class="comment">Meta property, render object or graphical table</p>
    </div>
</form>


<?php
if ($_POST) {

    // Stopwatch
    $time_pre = microtime(true);

    try {

        $sql = '';

        if (isset($_POST['select1_submit'])) {
            $sql = $_POST['select1'];
        } elseif (isset($_POST['select2_submit'])) {
            $sql = $_POST['select2'];
        } elseif (isset($_POST['selectJoin_submit'])) {
            $sql = $_POST['selectJoin'];
        } elseif (isset($_POST['select3_submit'])) {
            $sql = $_POST['select3'];
        }


        // Build command
        $command = new SharePointSqlCommand();
        $command->connection_name = "SharePointConnection1";
        $command->sql = $sql;

        // Execute command
        $result = new SharePointQuery($command);
        
        // If some form of exception occured and SILENT_EXCEPTIONS is set to TRUE
        if (!$result->commandStatus) {
            echo "<p>Silent exceptions is ON, this error message is forced to appear in this example</p>";
            $message = $result->exception->message;
            echo "<h2>Exception: $message</h2>";
            pr($result);
        } else {

            // Render result
            if (isset($_POST['renderAsTable'])) {

                // Load twig
                require_once '../twig/lib/Twig/Autoloader.php';
                Twig_Autoloader::register();
                $loader = new Twig_Loader_Filesystem('../templates');
                $twig = new Twig_Environment($loader);
                $template = $twig->loadTemplate('sp2007.html');

                echo $template->render(array(
                    'list_header' => $result->CamelotSoap->_sorted->_schema->display_name,
                    'list_content' => $result->CamelotSoap->_sorted->_content
                ));
            } else {
                // No fancy stuff, just dump the object
                pr($result);
            }
        }
    } catch (CamelotException $exc) {
        echo "<h2>Exception: $exc->message</h2>";
        pr($exc);
    }

    // ...end stopwatch
    $time_post = microtime(true);
    $exec_time = $time_post - $time_pre;
    echo "<p class=\"comment\">Query executed and data transfered in $exec_time seconds</p>";
}
?>

<?php include 'base/foot.html'; ?>